package kr.hkit.score_pjt.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.framwordk.dao.CloseDAOUtil;
import kr.hkit.framwordk.dao.LoadPropertyDAO;
import kr.hkit.score_pjt.DTO.StdInfo;



public class StdInfoDao {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public StdInfoDao() {
	}

	public StdInfoDao(Connection con) {
		this.con = con;
	}

	public void setCon(Connection con) {
		this.con = con;
	}
	
	public void insertProduct(StdInfo dto) throws SQLException {
		String sql = "insert into stdinfo values(?,?,?,?,?)";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dto.getStdNum());
		pstmt.setString(2, dto.getStdName());
		pstmt.setInt(3, dto.getKorScore());
		pstmt.setInt(4, dto.getEngScore());
		pstmt.setInt(5, dto.getMathScore());
		pstmt.executeUpdate();
		CloseDAOUtil.close((Connection) pstmt);
	}
	
	public ArrayList<StdInfo> selectAllStdInfoLists() throws Exception{
		String sql = "select grade, rank, stdnum, stdname, kor, eng, math, total, avg from view_stdinfo";
		ArrayList<StdInfo> lists = new ArrayList<>();
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		StdInfo std=null;
		while(rs.next()){
			std = new StdInfo();
			std.setStdGrade(rs.getInt("grade")).setStdRank(rs.getInt("rank"));
			std.setStdNum(rs.getInt("stdnum")).setStdName(rs.getString("stdname"));
			std.setKorScore(rs.getInt("kor")).setEngScore(rs.getInt("eng")).setMathScore(rs.getInt("math"));
			std.setTotal(rs.getInt("total")).setAvg(rs.getInt("avg"));
			lists.add(std);
		}
		return lists;
	}
	
	public void deleteAllStdInfo() throws SQLException{
		String sql = "delete from stdinfo";
		pstmt = con.prepareStatement(sql);
		pstmt.executeUpdate();
		CloseDAOUtil.close((Connection) pstmt);
	}
	
	public static void main(String[] args) throws Exception {
		StdInfoDao dao = new StdInfoDao(LoadPropertyDAO.getConnection());
		System.out.println(dao.selectAllStdInfoLists());
	}
}
